import psycopg2
import psycopg2.extras
import sys
import gdal
from gdalconst import *
import os
from shapely.wkt import dumps, loads
import datetime
from datetime import timedelta
import numpy as np
import constant

def select_npp_temp(in_file,time_range,neighbor_pixel):
    f = open(in_file, "w+")
    station_query='SELECT id, ST_AsText(location) as location FROM public.grdstation WHERE id>=1 and id <=5'
    #mod_query="SELECT mod04.aqstime, mod04.aod, mod07temp.temperature from (SELECT aqstime, st_neighborhood(rasref10, (st_GeomFromText('POINT({0} {1})', 4326)), 2, 2, true) as aod FROM apom.satresampmod04 where aqstime < '2014-09-10 00:00:00'::timestamp) as mod04 inner join (SELECT aqstime, st_neighborhood(rasref10, (st_GeomFromText('POINT({2} {3})', 4326)), 2, 2, true) as temperature FROM apom.satresampmod07temperature where aqstime < '2014-09-10 00:00:00'::timestamp) as mod07temp ON (mod04.aqstime = mod07temp.aqstime) order by mod04.aqstime asc"
    mod_query="SELECT mod04.aqstime, mod04.aod, mod07temp.temperature from (SELECT aqstime, st_neighborhood(rasref, (st_GeomFromText('POINT({0} {1})', 4326)), {2}, {2}, true) as aod FROM apom.satresampviirs) as mod04 inner join (SELECT aqstime, ST_Value(rasref, (st_GeomFromText('POINT({0} {1})', 4326)),true) as temperature FROM apom.satresampviirstemperature) as mod07temp ON (mod04.aqstime = mod07temp.aqstime) order by mod04.aqstime asc"
                                                  
    pm_query="SELECT avg(pm1), avg(pm25), avg(pm10) FROM apom.grdpmhiscem_data WHERE (pm1 > 0 OR pm25 > 0 OR pm10 > 0) and aqstime > '{0}'::timestamp - interval '{1} minutes' and aqstime < '{2}'::timestamp + interval '{3} minutes' and stationid = {4} GROUP BY aqstime"
    con = None
    try:
        # doc danh sach tram
        con = psycopg2.connect(host=constant.__HOST__, database=constant.__DBNAME__, user=constant.__USERNAME__, password=constant.__PASSWORD__) 
        cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
        cur.execute(station_query)
        stations = cur.fetchall()
        for station in stations:
            stationid = station["id"]
            # bo qua tram Khanh Hoa
            # if stationid  == 3:
            #     continue
                
            location = loads(station["location"])
        
            cur.execute(mod_query.format(location.x, location.y, neighbor_pixel))

         
            rows = cur.fetchall()
            for row in rows:
                aqstime = datetime.datetime.strptime(str(row["aqstime"]), "%Y-%m-%d %H:%M:%S") + timedelta(hours=7)
        
                # du lieu gia ve la 1 mang 2 chieu
                aod = row["aod"]
                aod_result = []
                for i in range(len(aod)):
                    # filter nhung gia tri != None thi se them vao mang ket qua
                    tmp = filter(lambda a: a != None, aod[i])
                    aod_result += tmp
                # calculate real aod value
                aod_value = (np.median(aod_result)) if len(aod_result) > 0 else 0
                    
                
                temperature = row["temperature"]
                if(temperature!=None):
                    temperature_value = temperature
                else:
                    temperature_value=0;
                    
#                 temperature = row["temperature"]
#                 temperature_result = []
#                 for i in range(len(temperature)):
#                     tmp = filter(lambda a: a != None, temperature[i])
#                     temperature_result += tmp
#                 temperature_value = (np.median(temperature_result) + 15000) * 0.00999999977648258 if len(temperature_result) > 0 else 0
#                
                if temperature_value > 0 and aod_value > 0:
                
                    cur.execute(pm_query.format(aqstime, time_range, aqstime, time_range, stationid))
                    
                    pm_row = cur.fetchone()
                    if not(pm_row is None):
                        pm1 = pm_row[0]
                        pm25 = pm_row[1]
                        pm10 = pm_row[2]
                     

                        f.write("{0},{1},{2},{3},{4},{5},{6},{7},{8}\n".format(stationid, aqstime.year, aqstime.month, aqstime, pm1, pm25, pm10, aod_value, temperature_value))
            
    except psycopg2.DatabaseError, e:
        print 'Error %s' % e    
        sys.exit(1)
    finally:
        if con:
            con.close()
        if f:
            f.close()



if __name__ == '__main__':

    #select_npp_temp(30)
    select_npp_temp("data/npp_data_30s_6km.csv",30,1)
    select_npp_temp("data/npp_data_30s_12km.csv",30,2)
    select_npp_temp("data/npp_data_30s_18km.csv",30,3)
    select_npp_temp("data/npp_data_30s_24km.csv",30,4)
    select_npp_temp("data/npp_data_30s_30km.csv",30,5)
    select_npp_temp("data/npp_data_30s_36km.csv",30,6)
    select_npp_temp("data/npp_data_30s_42km.csv",30,7)
    select_npp_temp("data/npp_data_30s_48km.csv",30,8)
    select_npp_temp("data/npp_data_30s_54km.csv",30,9)
    
    select_npp_temp("data/npp_data_60s_6km.csv",60,1)
    select_npp_temp("data/npp_data_60s_12km.csv",60,2)
    select_npp_temp("data/npp_data_60s_18km.csv",60,3)
    select_npp_temp("data/npp_data_60s_24km.csv",60,4)
    select_npp_temp("data/npp_data_60s_30km.csv",60,5)
    select_npp_temp("data/npp_data_60s_36km.csv",60,6)
    select_npp_temp("data/npp_data_60s_42km.csv",60,7)
    select_npp_temp("data/npp_data_60s_48km.csv",60,8)
    select_npp_temp("data/npp_data_60s_54km.csv",60,9)
   
   
    select_npp_temp("data/npp_data_90s_6km.csv",90,1)
    select_npp_temp("data/npp_data_90s_12km.csv",90,2)
    select_npp_temp("data/npp_data_90s_18km.csv",90,3)
    select_npp_temp("data/npp_data_90s_24km.csv",90,4)
    select_npp_temp("data/npp_data_90s_30km.csv",90,5)
    select_npp_temp("data/npp_data_90s_36km.csv",90,6)
    select_npp_temp("data/npp_data_90s_42km.csv",90,7)
    select_npp_temp("data/npp_data_90s_48km.csv",90,8)
    select_npp_temp("data/npp_data_90s_54km.csv",90,9)
   
   
    select_npp_temp("data/npp_data_720s_6km.csv",720,1)
    select_npp_temp("data/npp_data_720s_12km.csv",720,2)
    select_npp_temp("data/npp_data_720s_18km.csv",720,3)
    select_npp_temp("data/npp_data_720s_24km.csv",720,4)
    select_npp_temp("data/npp_data_720s_30km.csv",720,5)
    select_npp_temp("data/npp_data_720s_36km.csv",720,6)
    select_npp_temp("data/npp_data_720s_42km.csv",720,7)
    select_npp_temp("data/npp_data_720s_48km.csv",720,8)
    select_npp_temp("data/npp_data_720s_54km.csv",720,9)
   
   
